package dao.daoImpl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dao.BaseDao;
import dao.StudentDao;
import entity.Student;



public class StudentDaoimpl extends BaseDao implements StudentDao {

	 
	@Override
	public int insert(Student stu) {
		String sql="insert into s_student values(?,?,?,?,?,?)";
		try {
			PreparedStatement prst= con.prepareStatement(sql);
			prst.setInt(1, stu.getSno());
			prst.setString(2, stu.getSname());
			prst.setString(3, stu.getSex());
			prst.setInt(4, stu.getAge());
			prst.setString(5, stu.getMajor());
			prst.setInt(6, stu.getPassword());
			return prst.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1 ;
		}
		// TODO Auto-generated method stub
	}
	public int updataStudent(Student stu) {
	   String sql = "update s_student set  sname = ?, sex=? ,age=?,major=?,password=? where sno = ?";
	   try {
			PreparedStatement prst =con.prepareStatement(sql);
			prst.setString(1, stu.getSname());
			prst.setString(2, stu.getSex());
			prst.setInt(3, stu.getAge());
			prst.setString(4, stu.getMajor());
			prst.setInt(5, stu.getPassword());
			prst.setInt(6, stu.getSno());
			
			if(prst.executeUpdate() != 0) {
				return 1;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 return -1;
		
 }
	public Student searchStudent(Student stu)  { 
		String sql = "select * from s_student where sno = ? and password=?";
		try {
			PreparedStatement prst =con.prepareStatement(sql);
			prst.setInt(1, stu.getSno());
			prst.setInt(2, stu.getPassword());
			ResultSet res = prst.executeQuery();
			 stu = new Student();
			while(res.next()) {
				stu.setSno(res.getInt("sno"));
				stu.setSname(res.getString("sname"));
				stu.setSex(res.getString("sex"));
				stu.setAge(res.getInt("age"));
				stu.setMajor(res.getString("major"));
				stu.setPassword(res.getInt("password"));
        		System.out.println(stu);
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		/* System.out.println(stu.toString()); */
		
		return stu; 
	}
	 public List<Student>  selectAll() {
		   String sql = "select * from s_student ";
		   List<Student>  list=new ArrayList<Student>();
		  try {
			PreparedStatement prst=con.prepareStatement(sql);
			ResultSet res = null;
			res=prst.executeQuery();
			
			while(res.next()) {
			Student stu=new Student();
			stu.setSno(res.getInt("sno"));
			stu.setSname(res.getString("sname"));
			stu.setSex(res.getString("sex"));
			stu.setAge(res.getInt("age"));
			stu.setMajor(res.getString("major"));
			stu.setPassword(res.getInt("password"));
			System.out.println(stu.toString());
			list.add(stu);
		} 
			}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		  
		   return list ;
	   }
	 @Override
	public void deleteStudent(int sno) {
		String sql = "delete from s_student where sno = ?";//鎵ц鐨剆ql璇彞
		try {
			PreparedStatement prst =con.prepareStatement(sql);
			prst.setInt(1,sno);
			if(prst.executeUpdate() != 0) {
				System.out.println("鍒犻櫎鏁版嵁鎴愬姛锛�");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return;
	}
	@Override
	public Student searchById(int id) {
		String sql = "select * from s_student where sno = ? ";
		try {
			PreparedStatement prst =con.prepareStatement(sql);
			prst.setInt(1,id);

			ResultSet res = prst.executeQuery();
			Student stu = new Student();
			while(res.next()) {
				stu.setSno(res.getInt("sno"));
				stu.setSname(res.getString("sname"));
				stu.setSex(res.getString("sex"));
				stu.setAge(res.getInt("age"));
				stu.setMajor(res.getString("major"));
				stu.setPassword(res.getInt("password"));
        		System.out.println(stu);
        		return stu;
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		/* System.out.println(stu.toString()); */
		
		return null; 
	}
	@Override
	public List<Student> searchLike(String name, String major) {
		List<Student>  list=new ArrayList<Student>();
		try {	
			String sql="SELECT * FROM s_student WHERE 1=1";
			StringBuilder sb=new StringBuilder(sql);
			if(name!=null&&name.length()!=0) {
				sb.append(" and sname like '%"+name+"%' ");
			}
			if(major!=null&&major.length()!=0) {
				sb.append(" and major like '%"+major+"%' ");
			}
		
		PreparedStatement prst =con.prepareStatement(sb.toString());
			ResultSet res = prst.executeQuery();
		
			list.clear();
			while(res.next()) {
				Student stu = new Student();
				stu.setSno(res.getInt("sno"));
				stu.setSname(res.getString("sname"));
				stu.setSex(res.getString("sex"));
				stu.setAge(res.getInt("age"));
				stu.setMajor(res.getString("major"));
				stu.setPassword(res.getInt("password"));
				list.add(stu);
				}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	
}
